package com.xjrsoft.module.system.controller;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.meta.Column;
import cn.hutool.db.meta.MetaUtil;
import cn.hutool.db.meta.Table;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.toolkit.JdbcUtils;
import com.xjrsoft.common.annotation.XjrLog;
import com.xjrsoft.common.constant.GlobalConstant;
import com.xjrsoft.common.handler.FormContentStyleStrategy;
import com.xjrsoft.common.model.result.R;
import com.xjrsoft.common.page.ConventPage;
import com.xjrsoft.common.page.PageOutput;
import com.xjrsoft.common.utils.DatasourceUtil;
import com.xjrsoft.common.utils.ExcelUtil;
import com.xjrsoft.common.utils.VoToColumnUtil;
import com.xjrsoft.module.generator.entity.TableFieldConfig;
import com.xjrsoft.module.generator.entity.TableStructureConfig;
import com.xjrsoft.module.system.dto.*;
import com.xjrsoft.module.system.entity.Databaselink;
import com.xjrsoft.module.system.service.IDatabaselinkService;
import com.xjrsoft.module.system.vo.*;
import io.netty.util.internal.StringUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.sql.DataSource;
import javax.validation.Valid;
import javax.validation.constraints.NotNull;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 * 数据库连接表 前端控制器
 * </p>
 *
 * @author tzx
 * @since 2022-03-04
 */
@Api(tags = "数据库链接模块")
@RestController
@RequestMapping(GlobalConstant.SYSTEM_MODULE_PREFIX+ "/databaselink")
@AllArgsConstructor
public class DatabaselinkController {

    private final IDatabaselinkService databaselinkService;

    private final DynamicDataSourceProperties dataSourceProperties;

    /**
     * 分页
     */
    @GetMapping("/page")
    @ApiOperation(value = "查询数据库链接（分页）")
    public R page(@Valid DatabaseLinkPageDto dto){

        LambdaQueryWrapper<Databaselink> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.like(StrUtil.isNotBlank(dto.getKeyword()),Databaselink::getDbName,dto.getKeyword())
                    .like(StrUtil.isNotBlank(dto.getDbName()),Databaselink::getDbName,dto.getDbName())
                    .like(StrUtil.isNotBlank(dto.getDbType()),Databaselink::getDbType,dto.getDbType())
                    .orderByDesc(Databaselink::getCreateDate)
                    .select(Databaselink.class,x -> VoToColumnUtil.fieldsToColumns(DatabaselinkPageVo.class).contains(x.getProperty()));

        IPage<Databaselink> page = databaselinkService.page(ConventPage.getPage(dto), queryWrapper);
        PageOutput<DatabaselinkPageVo> pageOutput = ConventPage.getPageOutput(page, DatabaselinkPageVo.class);
        return R.ok(pageOutput);
    }

    @GetMapping
    @ApiOperation(value = "查询数据库链接（不分页）")
    public R list(@Valid DatabaseLinkListDto dto){

        LambdaQueryWrapper<Databaselink> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.like(StrUtil.isNotBlank(dto.getDbName()),Databaselink::getDbName,dto.getDbName())
                    .like(StrUtil.isNotBlank(dto.getDbType()),Databaselink::getDbType,dto.getDbType())
                    .select(Databaselink.class,x -> VoToColumnUtil.fieldsToColumns(DatabaselinkPageVo.class).contains(x.getProperty()));
        List<Databaselink> databaseLinkList = databaselinkService.list(queryWrapper);
        List<DatabaseLinkListVo> resultList = new ArrayList<>();
        resultList.add(BeanUtil.toBean(masterInfo().getData(), DatabaseLinkListVo.class));
        resultList.addAll(BeanUtil.copyToList(databaseLinkList, DatabaseLinkListVo.class));
        return R.ok(resultList);
    }


    @GetMapping("/master-info")
    @ApiOperation(value = "查询主数据库链接详情")
    public R masterInfo() {
        DatabaseLinkVo vo = new DatabaseLinkVo();
        String primary = dataSourceProperties.getPrimary();
        vo.setId(primary);
        vo.setDbName("默认数据库");
        DataSourceProperty dataSourceProperty = dataSourceProperties.getDatasource().get(primary);
        vo.setDbType(JdbcUtils.getDbType(dataSourceProperty.getUrl()).getDb());
        vo.setHost(dataSourceProperty.getUrl());
        vo.setUsername(dataSourceProperty.getUsername());
        vo.setDriver(dataSourceProperty.getDriverClassName());
        return R.ok(vo);
    }

    @GetMapping("/info")
    @ApiOperation(value = "查询数据库链接详情")
    public R info(@RequestParam Long id){
        Databaselink databaselink = databaselinkService.getById(id);
        if (databaselink == null) {
            R.error("找不到此链接详情！");
        }
        return R.ok(BeanUtil.toBean(databaselink, DatabaseLinkVo.class));
    }

    @PostMapping
    @ApiOperation(value = "新增数据库链接")
    public R add(@Valid @RequestBody AddDatabaseLinkDto dto){
        return R.ok(databaselinkService.add(dto));
    }

    @PutMapping
    @ApiOperation(value = "修改数据库链接")
    public R update(@Valid @RequestBody UpdateDatabaseLinkDto dto){

        return R.ok(databaselinkService.update(dto));
    }

    @PostMapping("/test")
    @ApiOperation(value = "修改数据库链接")
    public R test(@Valid @RequestBody AddDatabaseLinkDto dto){
        Databaselink databaselink = BeanUtil.toBean(dto, Databaselink.class);
        return R.ok(databaselinkService.test(databaselink));
    }

    @DeleteMapping
    @ApiOperation(value = "删除")
    public R delete(@RequestBody List<Long> ids){
        return R.ok(databaselinkService.removeBatchByIds(ids));
    }

    @GetMapping("/table")
    @ApiOperation(value = "获取当前数据库连接下的所有表")
    public R getTable(@RequestParam String id,@RequestParam(required = false) String tableName){
        return R.ok(databaselinkService.getTables(id,tableName));
    }

    @GetMapping("/table/data")
    @ApiOperation(value = "获取当前数据库连接下的所选表数据")
    public R getTableData(@RequestParam String id,@RequestParam(required = false) String tableName){
        return R.ok(databaselinkService.getTablesData(id,tableName));
    }


    @GetMapping("/table/columns")
    @ApiOperation(value = "获取当前数据库连接下某个表的所有字段")
    public R getTableColumn(@RequestParam String id,@RequestParam String tableName){
        return R.ok(databaselinkService.getTableColumns(id,tableName));
    }

    @GetMapping("/table/columns-name")
    @ApiOperation(value = "获取当前数据库连接下某个表的所有字段")
    public R getTableColumnName(@RequestParam String id,@RequestParam String tableName){
        return R.ok(databaselinkService.getTableColumnName(id,tableName));
    }

    @GetMapping("/table/columns/multi")
    @ApiOperation(value = "根据表名获取多个表字段信息")
    public R getMultiTableColumn(@RequestParam String id,@RequestParam String tableNames){
        return R.ok(databaselinkService.getMultiTableColumns(id,tableNames));
    }

    @GetMapping("/table/columns-name/multi")
    @ApiOperation(value = "根据表名获取多个表字段名")
    public R getMultiTableColumnName(@RequestParam String id,@RequestParam String tableNames){
        return R.ok(databaselinkService.getMultiTableColumnName(id,tableNames));
    }

    @GetMapping("/table/validate-name")
    @ApiOperation(value = "验证表名是否存在")
    public R validateTableName(@RequestParam String id, @RequestParam String tableNames){
        List<String> allTableNameList = MetaUtil.getTables(DatasourceUtil.getDataSource(id));
        String[] tableNameArray = StringUtils.split(tableNames, StringPool.COMMA);
        List<String> existTableList = new ArrayList<>();
        for (String validatedName : tableNameArray) {
            for (String tableName : allTableNameList) {
                if (StringUtils.equalsIgnoreCase(tableName, validatedName)) {
                    existTableList.add(validatedName);
                    break;
                }
            }
        }
        if (CollectionUtils.isNotEmpty(existTableList)) {
            return R.error("表名已存在：" + existTableList);
        }
        return R.ok();
    }

    @PostMapping("/table/validate")
    @ApiOperation(value = "验证表结构")
    @SneakyThrows
    public R validateTable(@RequestBody ValidateTableDto dto) {
        String databaseId = dto.getDatabaseId();
        DataSource dataSource = DatasourceUtil.getDataSource(databaseId);
        String databaseName =  null;
        DbType dbType = null;
        if (StrUtil.equalsIgnoreCase(databaseId, GlobalConstant.DEFAULT_DATASOURCE_KEY)) {
            databaseName = "默认数据库";
            dbType = DatasourceUtil.getDataSourceMasterDbType();
        } else {
            Databaselink databaselink = databaselinkService.getById(Long.valueOf(databaseId));
            databaselink.getDbName();
            dbType = JdbcUtils.getDbType(databaselink.getHost());
        }
        List<String> allTableNameList = MetaUtil.getTables(dataSource);
        List<ValidateTableResultVo> resultList = new ArrayList<>();
        for (TableStructureConfig tableStructureConfig : dto.getTableStructureConfigs()) {
            String tableName = tableStructureConfig.getTableName();
            ValidateTableResultVo resultVo = new ValidateTableResultVo();
            resultVo.setDatabaseName(databaseName);
            resultVo.setTableName(tableName);
            if (allTableNameList.stream().anyMatch(x -> StringUtils.equalsIgnoreCase(tableName, x))) {
                // 数据库表信息
                Table table = MetaUtil.getTableMeta(dataSource, tableName);
                // 添加主表主键
                TableFieldConfig pk = new TableFieldConfig();
                pk.setFieldName(GlobalConstant.DEFAULT_PK);
                pk.setFieldType(6);
                tableStructureConfig.getTableFieldConfigs().add(pk);
                if (!tableStructureConfig.getIsMain()) {
                    // 不是主表加入外键
                    TableFieldConfig fk = new TableFieldConfig();
                    fk.setFieldName(GlobalConstant.DEFAULT_FK);
                    fk.setFieldType(6);
                    tableStructureConfig.getTableFieldConfigs().add(fk);
                }
                if (tableStructureConfig.getTableFieldConfigs().size() != table.getColumns().size()) {
                    // 表字段数量不一致
                    resultVo.setStatus(2);
                }
                // 对比表字段差异
                if (resultVo.getStatus() == null) {
                    for (TableFieldConfig fieldConfig : tableStructureConfig.getTableFieldConfigs()) {
                        boolean isMatchField = false;
                        for (Column tableField : table.getColumns()) {
                            Integer fieldLength = fieldConfig.getFieldLength();
                            Integer type = fieldConfig.getFieldType();
                            String dataType = DatasourceUtil.getDbType(dbType, type);
                            if (StrUtil.equalsIgnoreCase(fieldConfig.getFieldName(), tableField.getName())
                                    && StrUtil.equalsIgnoreCase(dataType, tableField.getTypeName())
                                    && (type != 0 || fieldLength.equals((int)tableField.getSize()))) {
                                isMatchField = true;
                                break;
                            }
                        }
                        if (!isMatchField) {
                            resultVo.setStatus(2);
                            break;
                        }
                    }
                }
                if (resultVo.getStatus() == null) {
                    // 表结构相同
                    resultVo.setStatus(1);
                }
            }
            if (resultVo.getStatus() == null) {
                // 不存在该表
                resultVo.setStatus(3);
            }
            resultList.add(resultVo);
        }
        return R.ok(resultList);
    }

    @PostMapping(value = "/export")
    @ApiOperation(value = "导出")
    @XjrLog(value = "导出表单数据")
    public ResponseEntity<byte[]> export(@Valid @RequestBody List<Long> ids) {
        List<Databaselink> list = databaselinkService.list(Wrappers.<Databaselink>lambdaQuery().in(Databaselink::getId, ids));
        List<DatabaselinkExportVo> databaselinkExportVos = BeanUtil.copyToList(list, DatabaselinkExportVo.class);
        ByteArrayOutputStream bot = new ByteArrayOutputStream();
        EasyExcel.write(bot).head(DatabaselinkExportVo.class).automaticMergeHead(false)
                .registerWriteHandler(new FormContentStyleStrategy())
                .excelType(ExcelTypeEnum.XLSX).sheet().doWrite(databaselinkExportVos);
        ByteArrayOutputStream resultBot = ExcelUtil.renderExportRequiredHead(bot);
        return R.fileStream(resultBot.toByteArray(), "数据库链接" + ExcelTypeEnum.XLSX.getValue());
    }

    @GetMapping("/export")
    @ApiOperation(value = "下载模板")
    @SneakyThrows
    public ResponseEntity<byte[]> export() {
        List<DatabaselinkExportVo> databaselinkExportVoList = new ArrayList<>();
        ByteArrayOutputStream bot = new ByteArrayOutputStream();
        EasyExcel.write(bot).head(DatabaselinkExportVo.class).automaticMergeHead(false)
                .registerWriteHandler(new FormContentStyleStrategy())
                .excelType(ExcelTypeEnum.XLSX).sheet().doWrite(databaselinkExportVoList);
        ByteArrayOutputStream resultBot = ExcelUtil.renderExportRequiredHead(bot);
        return R.fileStream(resultBot.toByteArray(), "数据库链接模板" + ExcelTypeEnum.XLSX.getValue());
    }

    @PostMapping("/import")
    @ApiOperation(value = "导入")
    public R importData(@RequestParam MultipartFile file) throws IOException {
        List<DatabaselinkExportVo> databaselinkExportVoList = EasyExcel.read(file.getInputStream()).head(DatabaselinkExportVo.class).sheet().doReadSync();
        List<Databaselink> databaselinks = BeanUtil.copyToList(databaselinkExportVoList, Databaselink.class);
        return R.ok(databaselinkService.importLink(databaselinks));
    }

}
